import re

import pandas as pd

project_code = {
    "1-76项目": 1,
    "2-76派工单": 2,
    "3-80项目": 3,
    "4-80项目机加": 4,
    "5-80派工单": 5,
    "6-819项目": 6,
    "7-819机加、派工单项目": 7,
    "8-CC腹鳍项目": 8,
    "9-CC腹鳍机加派工单项目": 9,
    "10-E-尾翼项目": 10,
    "11-FY-胶结件": 11,
    "12-FY-胶结件随炉件": 12,
    "13-FY-小肋": 13,
    "14-FY-蒙皮": 14,
    "15-FY-蒙皮随炉件": 15,
    "16-FY-后梁随炉件": 16,
    "17-FY-小肋随炉件": 17,
    "18-FY-边肋随炉件": 18,
    "19-FY-后梁": 19,
    "20-FY-垫块": 20,
    "21-FY-历史机加": 21,
    "22-FY项目机加": 22,
    "23-FY-整流罩": 23,
    "24-FY-整流罩随炉件": 24,
    "25-FY-封边肋": 25,
    "26-FY-隐身罩": 26,
    "27-FY-派工单及返修": 27,
    "28-历史FY打包": 28,
    "29-特殊过程变更再确认典型件": 29,
    "30-FY-层压板5429": 30,
    "31-FY-层压板9611": 31,
    "32-GS": 32,
    "33-LS": 33,
    "34-LS-派工单": 34,
    "35-LS-机加": 35,
    "36-TTX-133天线项目": 36,
    "37-金属电磁微项目": 37,
    "38-其他": 38,
    "39-3.0项目": 39,
    "40-GS工艺过程卡片转厂": 40,
    "41-QJ项目": 41,
    "42-FJ": 42,
    "43-来料检验": 43,
    "44-A5": 44
}


def get_fo_number(project, file_number_old):
    # 获取项目代码并格式化为至少3位
    project_code_formatted = f"{project_code.get(project):>03}"
    # 格式化文件编号为至少5位
    try:
        cleaned_number = re.sub(r'\D', '', str(file_number_old))  # 提取数字
        file_number_formatted = f"{int(cleaned_number):05}" if cleaned_number else "00000"
    except ValueError:
        file_number_formatted = "00000"
    # 拼接结果
    return f"FO-{project_code_formatted}-{file_number_formatted}"


def toSql(data, project):
    # 生成插入 SQL 语句
    table_name = "kc_fo_scan_file"  # 替换为你的表名
    df = pd.DataFrame([data])

    df["type"] = project_code.get(project)
    # df["archival_no"] = df["archival_no"].apply(lambda x: get_fo_number(project, x))

    columns = ', '.join(df.columns)
    values = ', '.join([
        f"'{str(value) if pd.notna(value) else ''}'" for value in df.values.flatten()
    ])
    sql_insert = f"INSERT INTO {table_name} ({columns}) VALUES ({values});"
    return sql_insert


if __name__ == '__main__':

    fields_with_comments = {
        '类型': 'type',
        '平台': 'platform',
        '项目id': 'project_id',
        '项目': 'project_name',
        '项目版本号': 'project_code',
        '产品名称': 'root_product_name',
        '产品图号': 'chart_no',
        '产品编号': 'zlbh',
        '生产编号': 'plan_code',
        '卡片名称': 'fo_name',
        '卡片编号': 'fo_no',
        '批次': 'flot',
        '编制人': 'editor',
        '位置': 'save_dir',
        '备注': 'remark',
        'OA': 'oa_no',
        'OA号': 'oa_no',
        '启动日期': 'start_date',
        '结束时间': 'end_date',
        '档案编号': 'archival_no'
    }

    file_name_path = "C:\\Users\\xyh\\Desktop\\FO工艺卡片检索.xlsx"

    # 读取 Excel 文件
    df = pd.read_excel(file_name_path, sheet_name='表头')

    # 跳过空行和空列，去重
    df_cleaned = df.dropna(how='all').dropna(axis=1, thresh=1).drop_duplicates()

    # 将每一行数据存入列表
    data_list = []
    for index, row in df_cleaned.iterrows():
        data_dict = {key: value for key, value in row.items() if pd.notna(value)}
        data_list.append(data_dict)
    with open("C:\\Users\\xyh\\Desktop\\sql.sql", 'w', encoding='utf-8') as file:
        # 处理每个项目
        for data in data_list:
            project = data['项目']
            keys = fields_with_comments.keys()

            # 过滤掉项目名
            filtered_values = [value for key, value in data.items()
                               if pd.notna(value) and value in keys]

            # 检查是否有有效列
            if filtered_values:
                outPd = pd.read_excel(file_name_path, sheet_name=project, usecols=filtered_values)
                df_cleaned = outPd.dropna(how='all').dropna(axis=1, thresh=1)
                data_list = []
                # 打印清理后的数据
                for index, row in df_cleaned.iterrows():
                    data_list.append(row)

                for data in data_list:
                    converted_data = {fields_with_comments.get(key, key): value for key, value in data.items()}
                    sql = toSql(converted_data, project)
                    file.write(sql + '\n')

            else:
                print(f"项目 '{project}' 没有有效的列可读取。")
